/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package jsalon.repo;

import jamjun.infra.common.DBHelper;
import jamjun.infra.common.DataType;
import jamjun.infra.common.SqlParameter;
import jamjun.infra.exception.JJException;
import jamjun.infra.exception.JJValidateExeption;
import jamjun.infra.lang.JJDate;
import jamjun.infra.lang.JJString;
import jamjun.infra.model.Paging;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;
import jsalon.enumtype.Doctype;
import jsalon.enumtype.Gender;
import jsalon.model.Customer;
import jsalon.model.CustomerFilter;
import jsalon.model.Officer;

/**
 *
 * @author Java
 */
public class CustomerRepo implements jsalon.repo.irepo.ICustomerRepo {

    @Override
    public boolean delete(String domainID,String code,String userID) throws Exception {
        //<editor-fold desc="Validate parameter">
        if(JJString.IsNullOrEmty(domainID)){
            throw new Exception("Require domainID.");
        }
        if(JJString.IsNullOrEmty(code)){
            throw new Exception("Require code.");
        }
        if(JJString.IsNullOrEmty(userID)){
            throw new Exception("Require userID.");
        }
        //</editor-fold>
        Connection conn = null;    
        PreparedStatement pstmt = null;
        try{
            conn = DBHelper.getConnection(jamjun.infra.model.DBConnectionName.jsalon.Name());
            List<SqlParameter> SqlParamList;
            SqlParamList = new ArrayList<>();
            String sql = "delete from customer  where domainID=@domainID and code=@code";
            SqlParamList.add(new SqlParameter("@domainID",domainID, DataType.typeString));
            SqlParamList.add(new SqlParameter("@code",code, DataType.typeString));
            pstmt = DBHelper.setPreparedStatement(conn,sql,SqlParamList);
            pstmt.executeUpdate(); // execute insert statement
            conn.close();
            return true;
        }catch(JJValidateExeption  vex){
            throw vex;
        }catch(JJException jex){
            throw jex;
        }catch(Exception ex){
            throw ex;
        } finally {
            if(pstmt != null) {pstmt.close();}
            if(conn != null) {conn.close();} 
        } 
    }

    @Override
    public Customer update(Customer data) throws Exception {
        //<editor-fold desc="validate model">
        if(JJString.IsNullOrEmty(data.domainID)){
            throw new Exception("Require domainID.");
        }
        if(JJString.IsNullOrEmty(data.code)){
            throw new Exception("Require code.");
        }
        //</editor-fold>
        //<editor-fold desc="intial valiable">
        data.updateDate = new Date();
        //</editor-fold>
        Connection conn = null;    
        //Statement stmt = null;
        PreparedStatement pstmt = null;
        try { 
            List<SqlParameter> SqlParamList;
            SqlParamList = new ArrayList<>();
            conn = DBHelper.getConnection(jamjun.infra.model.DBConnectionName.jsalon.Name());
            String sql = "update customer set name=@name,gender=@gender,birthDate=@birthDate"
                    +",idCard=@idCard,updateDate=@updateDate,updateBy=@updateBy"
                    +" where domainID=@domainID and code=@code";
            SqlParamList.add(new SqlParameter("@domainID",data.domainID, DataType.typeString));
            SqlParamList.add(new SqlParameter("@code",data.code, DataType.typeString));
            SqlParamList.add(new SqlParameter("@name",data.name, DataType.typeString));
            SqlParamList.add(new SqlParameter("@gender",data.gender.getVal(), DataType.typeInt));
            SqlParamList.add(new SqlParameter("@birthDate",data.birthDate,DataType.typeDateTime));
            SqlParamList.add(new SqlParameter("@idCard",data.idCard, DataType.typeString)); 
            SqlParamList.add(new SqlParameter("@updateDate",data.updateDate, DataType.typeDateTime));
            SqlParamList.add(new SqlParameter("@updateBy",data.updateBy, DataType.typeString)); 
            
            pstmt = DBHelper.setPreparedStatement(conn,sql,SqlParamList);
            pstmt.executeUpdate(); // execute insert statement
            conn.close();
            return data;
        } catch (SQLException ex) {
            Logger.getLogger(CustomerRepo.class.getName()).log(Level.SEVERE, null, ex);
            throw new Exception(ex.getMessage());
        } finally {
            if(pstmt != null) {pstmt.close();}
            if(conn != null) {conn.close();} 
        } 
    }

    public CustomerRepo() {
    }
    
    @Override
    public Customer insert(Customer data) throws Exception,JJValidateExeption {
        try{
            //<editor-fold desc="mapping and validate">
                Date currentDate = JJDate.Now();
                //validate model
                data.createDate = currentDate;
                data.updateDate = currentDate;
                data.registerDate = currentDate;
                data = jamjun.infra.common.Validation.ValidateModel(data);
                //check duplicate key

            //</editor>
            data = this.insertTransection(data);
            return data;
        }catch(JJValidateExeption  vex){
            throw vex;
        }    
        catch(Exception ex){
            throw ex;
        }
    }
    
    private Customer insertTransection(Customer data) throws Exception,JJException {
        Connection conn = null;    
        //Statement stmt = null;
        PreparedStatement pstmt = null;
        PreparedStatement pstmtSysNumber = null;
        
        conn = DBHelper.getConnection(jamjun.infra.model.DBConnectionName.jsalon.Name());
        conn.setAutoCommit(false);
        try { 
            List<SqlParameter> SqlParamList;
            SqlParamList = new ArrayList<>();
            String sql = "insert into customer "
                    +"(domainID,code,name,gender,birthDate,idCard,image1,registerDate,uuid,createDate,createBy,updateDate,updateBy )"
                    +" values " 
                    +"(@domainID,@code,@name,@gender,@birthDate,@idCard,@image1,@registerDate,@uuid,@createDate,@createBy,@updateDate,@updateBy);";
            SqlParamList.add(new SqlParameter("@domainID",data.domainID, DataType.typeString));
            SqlParamList.add(new SqlParameter("@code",data.code, DataType.typeString));
            SqlParamList.add(new SqlParameter("@name",data.name, DataType.typeString));
            SqlParamList.add(new SqlParameter("@gender",data.gender.getVal(), DataType.typeInt));
            SqlParamList.add(new SqlParameter("@birthDate",data.birthDate,DataType.typeDateTime));
            SqlParamList.add(new SqlParameter("@idCard",data.idCard, DataType.typeString));
            SqlParamList.add(new SqlParameter("@image1",data.image1, DataType.typeString));
            SqlParamList.add(new SqlParameter("@registerDate",data.registerDate, DataType.typeDateTime));
            SqlParamList.add(new SqlParameter("@uuid",data.uuid, DataType.typeString));
            SqlParamList.add(new SqlParameter("@createDate",data.createDate, DataType.typeDateTime));
            SqlParamList.add(new SqlParameter("@createBy",data.createBy, DataType.typeString));    
            SqlParamList.add(new SqlParameter("@updateDate",data.updateDate, DataType.typeDateTime));
            SqlParamList.add(new SqlParameter("@updateBy",data.updateBy, DataType.typeString)); 
            
            pstmt = DBHelper.setPreparedStatement(conn,sql,SqlParamList);
            pstmt.executeUpdate(); // execute insert statement
            //<editor-fold desc="update sysnumber">
            SysNumberRepo sysNumberRepo = new SysNumberRepo();
            pstmtSysNumber = sysNumberRepo.updateRunning(data.domainID, Doctype.customer, conn);
            pstmtSysNumber.executeUpdate();
            //</editor-fold>
            conn.commit();
            conn.close();
            return data;
        } catch (SQLException ex) {
            Logger.getLogger(CustomerRepo.class.getName()).log(Level.SEVERE, null, ex);
            conn.rollback();
            throw new JJException().SetException(this, ex);
        }catch(Exception ex){
            throw new JJException().SetException(this, ex);
        }
        finally {
            if(pstmt != null) {pstmt.close();}
            if(conn != null) {conn.close();} 
        } 
    }

    @Override
    public List<Customer> getList(Paging paging,CustomerFilter modelFilter) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try { 
            conn = DBHelper.getConnection(jamjun.infra.model.DBConnectionName.jsalon.Name());
            //<editor-fold desc="model filter.">
            String sqlFilterCMD = "";
            String opSQL = " and ";
            List<SqlParameter> SqlParameterList = new ArrayList<>();
            if(!JJString.IsNullOrEmty(modelFilter.domainID)){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " domainID=@domainID ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.SetString("@domainID",modelFilter.domainID);
                SqlParameterList.add(sqlParameter);
            }else{
                throw new Exception("not have domainID.");
            }
            if(!JJString.IsNullOrEmty(modelFilter.code)){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " code=@code ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.SetString("@code",modelFilter.code);
                SqlParameterList.add(sqlParameter);
            }
            if(!JJString.IsNullOrEmty(modelFilter.name )){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " name=@name ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.SetString("@name",modelFilter.name);
                SqlParameterList.add(sqlParameter);
            }
            if(modelFilter.gender != null){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " gender=@gender ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.SetInt("@gender",modelFilter.gender.getVal());
                SqlParameterList.add(sqlParameter);
            }
            if(modelFilter.birthDate != null){
                sqlFilterCMD += (!JJString.IsNullOrEmty(sqlFilterCMD)) ? opSQL : "";
                sqlFilterCMD += " Date(birthDate)=Date(@birthDate) ";
                SqlParameter sqlParameter = new  SqlParameter();
                sqlParameter.setDate("@birthDate",modelFilter.birthDate);
                SqlParameterList.add(sqlParameter);
            }
            sqlFilterCMD = (!JJString.IsNullOrEmty(sqlFilterCMD)) ? " where " + sqlFilterCMD : "";
            //</editor-fold>
            //<editor-fold desc="paging data.">
            String sqlPaging = "select count(code) as totalItem from Customer "+ sqlFilterCMD;
            pstmt = DBHelper.setPreparedStatement(conn,sqlPaging,SqlParameterList);
            rs = pstmt.executeQuery();
            while(rs.next()){
                paging.TotalItem = rs.getInt("totalItem");
            }
            //</editor-fold>
            int end = paging.PageNo * paging.PageSize;
            int begin = end-paging.PageSize;
            String sql = "SELECT * FROM Customer " + sqlFilterCMD;
            sql = "select * from ("+sql+") as t1 LIMIT "+begin+","+end;
            pstmt = DBHelper.setPreparedStatement(conn,sql,SqlParameterList);
            rs = pstmt.executeQuery();
            List<Customer> modelList = new ArrayList<>();
            while(rs.next()){
               Customer model = new Customer();
               model.domainID = rs.getString("domainID");
               model.code = rs.getString("code");
               model.gender = Gender.fromValue(rs.getInt("gender"));
               model.name = rs.getString("name");
               model.birthDate = rs.getDate("birthDate");
               model.idCard = rs.getString("idCard");
               model.image1 = rs.getString("image1");
               model.registerDate = rs.getDate("registerDate");
               model.createDate = rs.getTimestamp("createDate");
               model.createBy = rs.getString("createBy");
               model.updateDate = rs.getTimestamp("updateDate");
               model.updateBy = rs.getString("updateBy");
               modelList.add(model);
            }
            rs.close();
            return modelList;
        } catch (SQLException ex) {
            Logger.getLogger(CustomerRepo.class.getName()).log(Level.SEVERE, null, ex);
            throw new Exception(ex.getMessage());
        } finally {
            if(rs != null){rs.close(); }
            if(conn != null){conn.close();}
        } 
    }
    
}
